#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql.cursors

# 用户名
myUser = "root"
# 密码
myPwd = "MyNewPass4!"
# host
myHost = "172.27.1.126"
# port
myPort = 3306
# lms库
db_lms = "lms3"
# exam库
db_exam = "exam3"
# core库
db_core = "core"

# core库连接
coreHost = "172.27.1.126"
corePort = 3306

# 每次循环删除数据量
data_limit = "50000"
# 计划列表集合
list_plan = {
    ""
}

# lms数据表集合
list_lms = {
    # 根据 trainplan_id进行删除
    "activity_card_detail",
    # 根据 trainplan_id进行删除
    "activity_card",
    # 根据 trainplan_id进行删除
    "plan_user_order_batch",
    # 根据 plan_id 进行删除
    "plan_user_order",
    # 根据train_plan_id进行删除
    "record_user_course",
    # 根据 trainplan_id进行删除
    "trainplan_publish",
    # 根据 trainplan_id进行删除
    "trainplan_relate_type",
    # 根据 trainplan_id进行删除
    "trainplan_user",
    # 根据 plan_id进行删除
    "trainplan_course_user",
    # 根据 trainplan_id进行删除
    "trainplan_course_lms",
    # 根据 trainplan_id进行删除
    "archives_offline",
    # 根据 trainplan_id进行删除
    "plan_org_amount",
    # 根据 trainplan_id进行删除
    "plan_org_amount_log",
    # 根据 trainplan_id进行删除
    "record_user_return_course",
    # 根据 trainplan_id进行删除
    "certificate_basic_info",
    # 根据 trainplan_id进行删除
    "trainplan_filter_lms",
    # 根据 id 进行删除
    "trainplan_lms"
}
# lms非直接相关表
list_lms_union = {
    # 联合trainplan_relate_type 根据 trainplan_id 删除
    "trainplan_type",
    # 联合plan_user_order根据 trainplan_id 删除
    "plan_user_order_detail",
    # 根据 certificate_basic_info 表中的id进行删除
    "certificate_detail",
    # 根据 order_id进行删除
    "plan_invoice_info",
    # 根据 order_no进行删除
    "plan_einvoice_info",
    # 根据 plan_user_order表中的 order_id 进行删除
    "plan_refund_info"
}
# exam数据表集合
list_exam = {
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_category_relation",
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_course",
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_knowledge",
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_marking_teacher",
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_paper_relation",
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_student_retest_info",
    # 根据 plan_id 进行删除
    "exam_user_info",
    # plan_id 关联到exam_id 根据 exam_id进行删除
    "exam_user_papers_info",
    # 根据 plan_id 进行删除
    "exam_user_record",
    # 根据 plan_id 进行删除
    "exam_basic_info"
}
# exam非直接相关表
list_exam_union = {
    # plan_id 关联到exam_id 再关联paper_id 根据 paper_id进行删除
    "exam_paper_knowledge",
    # plan_id 关联到exam_id 再关联paper_id 根据 paper_id进行删除
    "exam_paper_level_relation",
    # plan_id 关联到exam_id exam_paper_id 根据 exam_paper_id进行删除
    "exam_paper_org",
    # plan_id 关联到exam_id 再关联paper_id 根据 paper_id进行删除
    "exam_paper_question"
}
# core数据表集合
list_core = {
    # 根据 trainplan_id进行删除
    "trainplan_check",
    # 根据 trainplan_id进行删除
    "trainplan_filter",
    # 根据 trainplan_id进行删除
    "trainplan_course",
    # 根据 id 进行删除
    "trainplan"
}


# 连接并执行-返回数据
def exec_sql_rows(h, p, mdb, sql):
    # 连接数据库
    connect = pymysql.Connect(
        host=h,
        port=p,
        user=myUser,
        passwd=myPwd,
        db=mdb,
        charset='utf8'
    )
    # 获取游标
    cursor = connect.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    # 关闭连接
    cursor.close()
    connect.close()
    return rows


# 连接并执行-返回影响行数
def exec_sql_count(h, p, mdb, sql):
    # 连接数据库
    connect = pymysql.Connect(
        host=h,
        port=p,
        user=myUser,
        passwd=myPwd,
        db=mdb,
        charset='utf8'
    )
    # 获取游标
    cursor = connect.cursor()
    count = cursor.execute(sql)
    # 关闭连接
    cursor.close()
    connect.close()
    return count


# 逐条删除
def exec_sql_del(h, p, mdb, sqlList):
    # 连接数据库
    connect = pymysql.Connect(
        host=h,
        port=p,
        user=myUser,
        passwd=myPwd,
        db=mdb,
        charset='utf8'
    )
    # 获取游标
    cursor = connect.cursor()
    for s in sqlList:
        print(s)
        cursor.execute(s)
    connect.commit()
    # 关闭连接
    cursor.close()
    connect.close()


# 根据计划删除
for i in list_plan:
    # lms数据删除-删除联合查询数据
    for j in list_lms_union:
        del_flag_union = 1
        while del_flag_union > 0:
            count = 0
            if j == "plan_user_order_detail":
                sql_lms = "select a.id from " + j + " a inner join plan_user_order b on a.order_id = b.id where b.plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "trainplan_type":
                sql_lms = "select a.id from " + j + " a inner join trainplan_relate_type  b on a.id = b.trainplan_type_id where b.trainplan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "plan_refund_info":
                sql_lms = "select a.id from " + j + " a inner join plan_user_order  b on a.order_id = b.id WHERE b.plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "plan_invoice_info":
                sql_lms = "select a.id from " + j + " a inner join plan_user_order  b on a.order_id = b.id WHERE b.plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "plan_einvoice_info":
                sql_lms = "select a.einvoice_eno from " + j + " a inner join plan_user_order  b on a.order_no = b.id WHERE b.plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where einvoice_eno = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "certificate_detail":
                sql_lms = "select a.id from " + j + " a inner join certificate_basic_info  b on a.c_id = b.id WHERE b.plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            del_flag_union = count
            print(del_flag_union)
    # lms数据删除-删除相关单表数据
    for j in list_lms:
        del_flag = 1
        while del_flag > 0:
            count = 0
            if j == "trainplan_lms":
                sql_lms = "select id from " + j + " where id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "record_user_course" or j == "record_user_return_course":
                sql_lms = "select id from " + j + " where train_plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            elif j == "trainplan_course_user" or j == "plan_user_order" or j == "certificate_basic_info":
                sql_lms = "select id from " + j + " where plan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            else:
                sql_lms = "select id from " + j + " where trainplan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_lms)
                count += exec_sql_count(myHost, myPort, db_lms, sql_lms)
                ids = exec_sql_rows(myHost, myPort, db_lms, sql_lms)
                print(ids)
                sql_lms_del = set()
                for d in ids:
                    sql_lms_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_lms_del)
                exec_sql_del(myHost, myPort, db_lms,sql_lms_del)
            del_flag = count
            print(del_flag)
    print(db_lms + " was deleted!")
    # 根据计划id查询考试列表
    sql_exam_ids = "select id from exam_basic_info where plan_id = '" + i + "';"
    print(sql_exam_ids)
    exam_ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam_ids)
    print(exam_ids)
    # exam数据删除-删除联合查询数据
    for j in list_exam_union:
        for e in exam_ids:
            # 根据examId查询出相关paperId
            sql_paper_ids = "select paper_id from exam_paper_relation where exam_id = '" + e[0] + "';"
            paper_ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam_ids)
            print(sql_paper_ids)
            for p in paper_ids:
                del_flag_union = 1
                while del_flag_union > 0:
                    count = 0
                    if j == "exam_paper_org":
                        sql_exam = "select id from " + j + " where exam_paper_id = '" + p[0] + "' limit " + data_limit + ";"
                        print(sql_exam)
                        count += exec_sql_count(myHost, myPort, db_exam, sql_exam)
                        ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam)
                        print(ids)
                        sql_exam_del = set()
                        for d in ids:
                            sql_exam_del.add("delete from " + j + " where id = '" + d[0] + "';")
                        print(sql_exam_del)
                        exec_sql_del(myHost, myPort, db_exam, sql_exam_del)
                    else:
                        sql_exam = "select paper_id from " + j + " where paper_id = '" + p[0] + "' limit " + data_limit + ";"
                        print(sql_exam)
                        count += exec_sql_count(myHost, myPort, db_exam, sql_exam)
                        ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam)
                        print(ids)
                        sql_exam_del = set()
                        for d in ids:
                            sql_exam_del.add("delete from " + j + " where paper_id = '" + d[0] + "';")
                        print(sql_exam_del)
                        exec_sql_del(myHost, myPort, db_exam, sql_exam_del)
                    del_flag_union = count
                    print(del_flag_union)
    # exam数据删除-删除相关单表数据
    for j in list_exam:
        for e in exam_ids:
            del_flag = 1
            while del_flag > 0:
                count = 0
                if j == "exam_user_info" or j == "exam_user_papers_info" or j == "exam_user_record" or j == "exam_papers_basic_info":
                    sql_exam = "select id from " + j + " where exam_id = '" + e[0] + "' limit " + data_limit + ";"
                    print(sql_exam)
                    count += exec_sql_count(myHost, myPort, db_exam, sql_exam)
                    ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam)
                    print(ids)
                    sql_exam_del = set()
                    for d in ids:
                        sql_exam_del.add("delete from " + j + " where id = '" + d[0] + "';")
                    print(sql_exam_del)
                    exec_sql_del(myHost, myPort, db_exam, sql_exam_del)
                elif j == "exam_basic_info":
                    sql_exam = "select id from " + j + " where id = '" + e[0] + "' limit " + data_limit + ";"
                    print(sql_exam)
                    count += exec_sql_count(myHost, myPort, db_exam, sql_exam)
                    ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam)
                    print(ids)
                    sql_exam_del = set()
                    for d in ids:
                        sql_exam_del.add("delete from " + j + " where id = '" + d[0] + "';")
                    print(sql_exam_del)
                    exec_sql_del(myHost, myPort, db_exam, sql_exam_del)
                else:
                    sql_exam = "select exam_id from " + j + " where exam_id = '" + e[0] + "' limit " + data_limit + ";"
                    print(sql_exam)
                    count += exec_sql_count(myHost, myPort, db_exam, sql_exam)
                    ids = exec_sql_rows(myHost, myPort, db_exam, sql_exam)
                    print(ids)
                    sql_exam_del = set()
                    for d in ids:
                        sql_exam_del.add("delete from " + j + " where exam_id = '" + d[0] + "';")
                    print(sql_exam_del)
                    exec_sql_del(myHost, myPort, db_exam, sql_exam_del)
                del_flag = count
                print(del_flag)
    print(db_exam + " was deleted!")
    # core数据删除-删除相关单表数据
    for j in list_core:
        del_flag = 1
        while del_flag > 0:
            count = 0
            if j == "trainplan":
                sql_core = "select id from " + j + " where id = '" + i + "' limit " + data_limit + ";"
                print(sql_core)
                count += exec_sql_count(coreHost, corePort, db_core, sql_core)
                ids = exec_sql_rows(coreHost, corePort, db_core, sql_core)
                print(ids)
                sql_core_del = set()
                for d in ids:
                    sql_core_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_core_del)
                exec_sql_del(coreHost, corePort, db_core, sql_core_del)
            else:
                sql_core = "select id from " + j + " where trainplan_id = '" + i + "' limit " + data_limit + ";"
                print(sql_core)
                count += exec_sql_count(coreHost, corePort, db_core, sql_core)
                ids = exec_sql_rows(coreHost, corePort, db_core, sql_core)
                print(ids)
                sql_core_del = set()
                for d in ids:
                    sql_core_del.add("delete from " + j + " where id = '" + d[0] + "';")
                print(sql_core_del)
                exec_sql_del(coreHost, corePort, db_core, sql_core_del)
            del_flag = count
            print(del_flag)
    print(db_core + " was deleted!")
